package managers;

import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import java.util.ArrayList;

import business.Company;
import business.Profile;
import business.User;
import conn.Conn;
import util.Cleaner;
import util.Table;
//import util.EDate;

public class MngUsers extends Manager {
	
	public MngUsers() {
		
	}
	
	public int login(String user, String pass) {
		int userID = 0;
		
		try {
			Statement stm = Conn.getConn().createStatement();
			String sql = "SELECT id FROM users WHERE username = '" + Cleaner.sqlCleaner(user) +
							"' AND password = '" + Cleaner.sqlCleaner(pass) + "'";
			
			rst = stm.executeQuery(sql);
			
			if (rst.next()) userID = rst.getInt("id");
			
			//rst.close();
			//stm.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return userID;
	}
	
	public User getUser(int userID) {
		User usr = new User();
		
		String sql;
		sql = "SELECT usr.username, usr.password, usr.name, usr.email, prf.id AS profileID, prf.profile " +
			  "FROM users usr " +
			  "INNER JOIN profiles prf ON usr.profile = prf.id " +
			  "WHERE usr.id = " + userID;
		//System.out.print(sql);
		try {
			rst = stmt.executeQuery(sql);
			
			if (rst.next()) {
				usr.setUserID(userID);
				usr.setUsername(rst.getString("username"));
				usr.setPass(rst.getString("password"));
				usr.setName(rst.getString("name"));
				usr.setEmail(rst.getString("email"));
				
				Profile prf = new Profile();
				prf.setId(rst.getInt("profileID"));
				prf.setProfile(rst.getString("profile"));
				
				usr.setProfile(prf);
				
				sql = "SELECT cmy.id, cmy.name, cmy.address, cmy.location " +
					  " FROM management mng" +
					  " INNER JOIN companies cmy ON mng.company = cmy.id" +
					  " WHERE mng.user = " + userID;
				//System.out.print(sql);
				rst = stmt.executeQuery(sql);

				ArrayList<Company> cmpys = new ArrayList<Company>();
				
				while (rst.next()) {
					Company cmpy = new Company();
					cmpy.setId(rst.getInt("id"));
					cmpy.setName(rst.getString("name"));
					cmpy.setAddress(rst.getString("address"));
					//cmpy.setLocation(rst.getInt("location"));
					//cmpy.setInventoryDate(new EDate(rst.getString("cmy.inventoryDate")));
					
					cmpys.add(cmpy);
				}
				usr.setCompany(cmpys);
			}	else {
				usr = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			usr = null;
		}
		
		return usr;
	}
	
	public boolean deleteUser(int userID) {
		boolean res = false;
		
		String sqlMng = "DELETE management.* FROM management WHERE user = " + userID;
		String sql = "DELETE users.* FROM users WHERE id = " + userID;
		
		try {
			Statement stm = Conn.getConn().createStatement();
			if (stm.executeUpdate(sqlMng) > 0) {
				if (stm.executeUpdate(sql) > 0) res = true;
			} 
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return res;
	}
	
	public boolean saveUser(String user, String pass, String name, String email, int idProfile, String emps, int userID) {
		User usr = new User();
		usr.setUserID(userID);
		usr.setUsername(user);
		usr.setPass(pass);
		usr.setName(name);
		usr.setEmail(email);
		
		Profile prf = new Profile();
		prf.setId(idProfile);
	
		usr.setProfile(prf);
		
		ArrayList<Company> companies = new ArrayList<Company>();
		
		String[] arrEmps = emps.split(",");
		int ne = arrEmps.length;
		int e;
		
		for (e = 0; e < ne; e ++) {
			Company cmpy = new Company();
			cmpy.setId(Integer.parseInt(arrEmps[e]));
			companies.add(cmpy);
		}
		usr.setCompany(companies);
		
		return saveUser(usr);
	}
	
	public boolean saveUser(User usr) {
		boolean res = false;
		String sql;
		
		if (usr.getUserID() > 0) {
			sql = "UPDATE users SET username = '" + usr.getUsername() + "', " +
				  "password = '" + usr.getPass() + "', name = '" + usr.getName() + "', " +
				  "email = '" + usr.getEmail() + "', profile = " + usr.getProfile().getId() +
				  " WHERE id = " + usr.getUserID();
		}	else {
			sql = "INSERT INTO users (username, password, name, email, profile) " +
				   	 "VALUES ('" + usr.getUsername() + "', '" + usr.getPass() + "', '" + usr.getName() + "', " +
				   	 "'" + usr.getEmail() + "', " + usr.getProfile().getId() + ")";
		}
		 
        if (execUpdate(sql)) {

            String sqlDel = "DELETE management.* FROM management WHERE user = " + usr.getUserID();
            execUpdate(sqlDel);

            String sqlMng = "INSERT INTO management (user, company) VALUES ";

            ArrayList<Company> companies = usr.getCompany();
            Iterator<Company> iteCmpys = companies.iterator();

            while (iteCmpys.hasNext()) {
                Company cmpy = iteCmpys.next();
                sqlMng += " (" + usr.getUserID() + ", " + cmpy.getId() + "), ";
            }

            sqlMng = sqlMng.substring(0, sqlMng.length() - 2);

            //System.out.println(sqlMng);
            if (execUpdate(sqlMng)) res = true;
        }
		
		return res;
	}
	
	public String getTable() {
		String sql, tbl = "";
		
		sql = "	SELECT usr.id, usr.username, usr.name, prf.profile" +
			  "	FROM users usr " +
			  "	INNER JOIN profiles prf ON usr.profile = prf.id";

        return Table.getTable(sql, "username,name,profile", "editUser", "id", "delUser", "id");
	}
}